# This is a singleton class for sqlite database connection.
import sqlite3
import logging

logging.basicConfig(level = logging.INFO,format = '%(asctime)s - %(name)s - %(levelname)s - %(message)s')

logger = logging.getLogger(__name__)

class SqliteSingleton:
    
    def __enter__(self):

        self.conn = sqlite3.connect('elite_notice.db')
        self.conn.row_factory = self.dict_factory
        return self.conn

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type:
            logger.error("Error occurred in PostgreSingleton")
            logger.error(exc_type, exc_val, exc_tb)
        else:
            self.conn.commit()
            self.conn.close()

    @staticmethod
    def dict_factory(cursor, row):
        d = {}
        for idx, col in enumerate(cursor.description):
            d[col[0]] = row[idx]
        return d


if __name__ == "__main__":

    #调用示例
    import os

    # init database
    with SqliteSingleton() as conn:
        script_path = "init_db.sql"  
        logger.info(os.path.exists(script_path))
        if os.path.exists(script_path):
            logger.info("init database from script")
            with open(script_path, 'r', encoding='utf-8') as file:
                cur = conn.cursor()
                sql_script = file.read()
                cur.executescript(sql_script)  # 执行 SQL 脚本

    with SqliteSingleton() as conn:
        cur =  conn.cursor()
        cur.execute("select * from notification_types")  # 执行 SQL 查询
        rows = cur.fetchall()  # 检索查询结果

    logger.info(rows)